package org.junwei.common.util;

import java.util.Iterator;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

/**
 * excel操作工具类
 * @author weijunbin
 * @date 2018-10-29
 */
public class ExcelUtils {
	
	/**
	 * 是否excel文件(根据文件名后缀判断)
	 * @param fileName
	 * @throws IllegalArgumentException fileName为null时发生
	 */
	public static Boolean isExcelFile(String fileName) {
		if(fileName == null) throw new IllegalArgumentException("isExcelFile(String fileName): Null FileName");
		if (fileName.lastIndexOf(".xlsx") > 0 || fileName.lastIndexOf(".xls") > 0) return true;
		return false;
	}
	
	/**
	 * 获取字符串类型单元格值
	 * @param cell
	 * @throws IllegalArgumentException Cell为null时发生
	 */
	public static String getStringCellValue(Cell cell) {
		if(cell == null) throw new IllegalArgumentException("getStringCellValue(Cell cell): Null Cell");
		cell.setCellType(CellType.STRING);
		return cell.getStringCellValue();
	}
	
	/**
	 * 获取布尔类型单元格值
	 * @param cell
	 * @throws IllegalArgumentException Cell为null时发生
	 */
	public static Boolean getBooleanCellValue(Cell cell) {
		if(cell == null) throw new IllegalArgumentException("getBooleanCellValue(Cell cell): Null Cell");
		cell.setCellType(CellType.BOOLEAN);
		return cell.getBooleanCellValue();
	}
	
	/**
	 * 获取数值类型单元格值
	 * @param cell
	 * @throws IllegalArgumentException Cell为null时发生
	 * @throws NumberFormatException 单元格值无法解析为Double时发生
	 */
	public static Double getNumericCellValue(Cell cell) {
		if(cell == null) throw new IllegalArgumentException("getNumericCellValue(Cell cell): Null Cell");
		
		cell.setCellType(CellType.STRING);
		String value = cell.getStringCellValue();
		if(StringUtils.isBlank(value)) return null;
		return Double.parseDouble(value);
	}
	
	/**
	 * 返回行内容
	 * @param row
	 * @throws IllegalArgumentException Row为null时发生
	 */
	public static String getContent(Row row) {
		if(row == null) throw new IllegalArgumentException("getContent(Row row): Null Row");
		Iterator<Cell> ite = row.cellIterator();
		StringBuffer buf = new StringBuffer("");
		while (ite.hasNext()) {
			buf.append(ite.next() + ",");
		}
		if (buf.length() > 0) {
			buf.substring(0, buf.length() - 1);
		}
		return buf.toString();
	}
	
	/**
	 * 获取单元格值
	 * @param row 行
	 * @param column 获取单元格列号
	 * @return
	 */
	public static Object getCellValue(Row row, int column){
		if(row == null) throw new IllegalArgumentException("getCellValue(Row row, int column): Null Row");
		if(column < 0 || column >= row.getLastCellNum()) 
			throw new IndexOutOfBoundsException("getCellValue(Row row, int column): " + column);
		
		Object val = "";
		Cell cell = row.getCell(column);
		if(cell == null) throw new IllegalArgumentException("getCellValue(Row row, int column): 指定的单元格为null");
		if (cell.getCellType() == CellType.NUMERIC) val = cell.getNumericCellValue();
		if (cell.getCellType() == CellType.STRING) val = cell.getStringCellValue();
		if (cell.getCellType() == CellType.FORMULA) val = cell.getCellFormula();
		if (cell.getCellType() == CellType.BOOLEAN) val = cell.getBooleanCellValue();
		if (cell.getCellType() == CellType.ERROR) val = cell.getErrorCellValue();
		return val;
	}
}
